package com.glp.work.flow.utils;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.glp.work.flow.contract.builder.ContractSource;

public class ExcelUtils {
    /**
     * 通过模板生成XLS
     *
     * @param templatePath
     * @param SheetName
     * @param dlistMap     动态数据（key为模板中#list=右侧名称，list<Objet>的顺序为模板中字段的顺序）
     * @param sMap         静态数据（）
     * @param endPoint     边界坐标。行轴:列轴 例：1:10 2行11列
     * @throws EncryptedDocumentException
     * @throws InvalidFormatException
     * @throws IOException                注：暂不支持定义多动态list数据
     */
    public static Workbook bulidXlsByTemplate(String templatePath, String SheetName,
                                              Map<String, List<List<Object>>> dlistMap, Map<String, Object> sMap, String endPoint)
            throws EncryptedDocumentException, InvalidFormatException, IOException {
        NumberFormat nf = NumberFormat.getNumberInstance();
        nf.setMaximumFractionDigits(2);
        InputStream inp = ContractSource.class.getResourceAsStream(templatePath);
        Workbook wb = WorkbookFactory.create(inp);
        Map<String, String> zbMap = new HashMap<String, String>();
        Sheet sheet = null;
        if (StringUtils.isEmpty(SheetName)) {
            sheet = wb.getSheetAt(0);
        } else {
            sheet = wb.getSheet(SheetName);
        }
        String[] eps = endPoint.split(":");
        for (int i = 0; i <= Integer.parseInt(eps[0]); ++i) {

            Row row = sheet.getRow(i);
            if (row == null) continue;
            for (int j = 0; j <= Integer.parseInt(eps[1]); ++j) {
                Cell cell = row.getCell(0);
                if (cell == null) continue;
                String flage = cell.getStringCellValue();
                if (flage.indexOf("#") != -1) {
                    zbMap.put(flage.split("=")[1], i + ":" + j);//行:列
                }
            }
        }


        if (dlistMap != null) {//生成动态数据
            //此处设置数据格式

            DataFormat df = wb.createDataFormat();
            for (String key : dlistMap.keySet()) {
                List<List<Object>> list = dlistMap.get(key);
                if (list == null) continue;
                int rowNum = Integer.valueOf(zbMap.get(key).split(":")[0]);
                Row row = sheet.getRow(rowNum);
                for (int i = 0; i < list.size(); ++i) {
                    sheet.setColumnWidth(i, 6000);
                    List<Object> cellDatas = list.get(i);
                    for (int j = 0; j < cellDatas.size(); ++j) {
                        Cell cell = row.getCell(j) == null ? row.createCell(j) : row.getCell(j);
                        Object o = cellDatas.get(j);
                        if (o instanceof String) {
                            cell.setCellValue(o.toString());
                            cell.setCellType(CellType.STRING);
                        } else if (o instanceof BigDecimal) {
                            CellStyle cellStyle = wb.createCellStyle();

                            //小数点后保留两位，
                            cellStyle.setDataFormat(df.getFormat("#,#0.00"));
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(((BigDecimal) o).doubleValue());
                            cell.setCellType(CellType.NUMERIC);
                        } else if (o instanceof Double) {
                            CellStyle cellStyle = wb.createCellStyle();
                            //小数点后保留两位，
                            cellStyle.setDataFormat(df.getFormat("#,#0.00"));
                            cell.setCellStyle(cellStyle);
                            //cell.setCellValue(nf.format(o));
                            cell.setCellValue((Double) o);
                            cell.setCellType(CellType.NUMERIC);
                        } else if (o instanceof Integer) {
                            CellStyle cellStyle = wb.createCellStyle();
                            //小数点后保留两位，
                            cellStyle.setDataFormat(df.getFormat("#,#0.00"));
                            cell.setCellStyle(cellStyle);

                            cell.setCellValue((Integer) o);
                            cell.setCellType(CellType.NUMERIC);
                        }
                    }
                    ++rowNum;
                    row = sheet.getRow(rowNum) == null ? sheet.createRow(rowNum) : sheet.getRow(rowNum);
                }
            }
        }

        if (sMap != null) {//生成静态数据

        }
        return wb;
    }

    public static Workbook bulidSheet(Workbook wb, String SheetName,
                                      Map<String, List<List<Object>>> dlistMap, Map<String, Object> sMap, String endPoint)
            throws EncryptedDocumentException {

//	InputStream inp = ContractSource.class.getResourceAsStream(templatePath);
//	Workbook wb = WorkbookFactory.create(inp);
//	if (rowCellStart == null){
//	  rowCellStart=0;
//	}
        NumberFormat nf = NumberFormat.getNumberInstance();
        nf.setMaximumFractionDigits(2);
        Map<String, String> zbMap = new HashMap<String, String>();
        Sheet sheet = null;
        if (StringUtils.isEmpty(SheetName)) {
            sheet = wb.getSheetAt(0);
        } else {
            sheet = wb.getSheet(SheetName);
        }
        String[] eps = endPoint.split(":");
        for (int i = 0; i <= Integer.parseInt(eps[0]); ++i) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            for (int j = 0; j <= Integer.parseInt(eps[1]); ++j) {
                Cell cell = row.getCell(0);
                if (cell == null) continue;
                String flage = cell.getStringCellValue();
                if (flage.indexOf("#") != -1) {
                    zbMap.put(flage.split("=")[1], i + ":" + j);//行:列
                }
            }
        }

        if (dlistMap != null) {//生成动态数据
            //此处设置数据格式
            DataFormat df = wb.createDataFormat();
            for (String key : dlistMap.keySet()) {
                List<List<Object>> list = dlistMap.get(key);
                if (list == null) continue;
                int rowNum = Integer.valueOf(zbMap.get(key).split(":")[0]);
                Row row = sheet.getRow(rowNum);
                for (int i = 0; i < list.size(); ++i) {
                    List<Object> cellDatas = list.get(i);
                    for (int j = 0; j < cellDatas.size(); ++j) {
                        Cell cell = row.getCell(j) == null ? row.createCell(j) : row.getCell(j);
                        Object o = cellDatas.get(j);
                        if (o instanceof String) {
                            cell.setCellValue(o.toString());
                            cell.setCellType(CellType.STRING);
                        } else if (o instanceof BigDecimal) {
                            CellStyle cellStyle = wb.createCellStyle();
                            //小数点后保留两位，
                            cellStyle.setDataFormat(df.getFormat("#,#0.00"));
                            cell.setCellValue(((BigDecimal) o).doubleValue());
                            cell.setCellType(CellType.NUMERIC);
                        } else if (o instanceof Double) {
                            CellStyle cellStyle = wb.createCellStyle();
                            //小数点后保留两位，
                            cellStyle.setDataFormat(df.getFormat("#,#0.00"));
                            //cell.setCellValue(nf.format(o));
                            cell.setCellValue((Double) o);
                            cell.setCellType(CellType.NUMERIC);
                        } else if (o instanceof Integer) {
                            CellStyle cellStyle = wb.createCellStyle();
                            //小数点后保留两位，
                            cellStyle.setDataFormat(df.getFormat("#,#0.00"));
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue((Integer) o);
                            cell.setCellType(CellType.NUMERIC);
                        }
                    }
                    ++rowNum;
                    row = sheet.getRow(rowNum) == null ? sheet.createRow(rowNum) : sheet.getRow(rowNum);
                }
            }
        }

        if (sMap != null) {//生成静态数据

        }
        return wb;
    }


}
